package db1;

import lombok.Getter;
import lombok.Setter;
import beans1.Patientbean;

import java.sql.*;
import java.util.ArrayList;


public class PatientDAO extends ConnBean{
    private Connection connection=null;

    public ArrayList<Patientbean> getAllRecords(){
        ResultSet rs=null;
        Statement stmt=null;
        ArrayList<Patientbean> list= new ArrayList<Patientbean>();
        try {
            //获取数据库连接(url,username,password)
            connection = getConnection();
            if(connection==null)
                System.out.println("error");
            //准备执行数据库查询
            String sql="select * from YY_PATIENT";
            //准备SQL执行;
            stmt =connection.createStatement();
            //执行数据库查询，获取结果集
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                Patientbean user=new Patientbean();
                user.setAccount(rs.getString("account"));
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("name"));
                user.setIdNumber(rs.getString("IdNumber"));
                user.setSex(rs.getString("Sex"));
                user.setBirthdate(rs.getDate("Birthdate"));
                user.setTelnumber(rs.getString("telnumber"));
                list.add(user);
            }
            //关闭表达式对象
            stmt.close();
            //关闭数据库
            connection.close();

        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        return list;
    }
//    public ArrayList<Patientbean> getRecord(String account){
//        ResultSet rs=null;
//        Statement stmt=null;
//        ArrayList<Patientbean> list=new ArrayList<Patientbean>();
//        try {
//            //获取数据库连接(url,username,password)
//            connection = getConnection();
//            if(connection==null)
//                System.out.println("error");
//            //准备执行数据库查询
//            String sql="select * from YY_PATIENT WHERE ACCOUNT=";
//            //准备SQL执行;
//            stmt =connection.createStatement();
//            //执行数据库查询，获取结果集
//            rs=stmt.executeQuery(sql);
//            while(rs.next()){
//                Patientbean user=new Patientbean();
//                user.setAccount(rs.getString("account"));
//                user.setPassword(rs.getString("password"));
//                user.setName(rs.getString("name"));
//                user.setIdNumber(rs.getString("IdNumber"));
//                user.setSex(rs.getString("Sex"));
//                user.setBirthdate(rs.getDate("Birthdate"));
//                user.setTelnumber(rs.getString("telnumber"));
//                list.add(user);
//            }
//            //关闭表达式对象
//            stmt.close();
//            //关闭数据库
//            connection.close();
//
//        }catch (SQLException e) {
//            e.printStackTrace();
//        } finally{
//            try{
//                if (connection!=null && (!connection.isClosed())){
//                    connection.close();
//                }
//            }catch(SQLException e){
//                e.printStackTrace();
//            }
//        }
//        return list;
//    }


    public boolean deleteRecord(Patientbean user) {
        PreparedStatement pstmt = null;
        String sql = "delete from YY_PATIENT where ACCOUNT=?";

        if (user == null)
            return false;
        int result = 0;
        try {
            connection = getConnection();
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, user.getAccount());
            result = pstmt.executeUpdate();

            System.out.println(user.getAccount());
            //关闭表达式对象
            pstmt.close();
            //关闭数据库
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null && (!connection.isClosed())) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (result > 0)
            return true;
        else
            return false;
    }
    public boolean editPatient(Patientbean user) throws SQLException {
        connection = getConnection();
//        String sql="update YY_PATIENT set password=?,name=?,idNumber=?,sex=?,birthDate=?,TELNUMBER=? where account=?";
        //预置对象YY_PATIENT
        String sql="update YY_PATIENT set password=?,name=?,idNumber=?,sex=?,TELNUMBER=? where account=?";

        PreparedStatement pstmt=connection.prepareStatement(sql);
        //设置占位符值
        pstmt.setString(1, user.getPassword());
        pstmt.setString(2, user.getName());
        pstmt.setString(3, user.getIdNumber());
        pstmt.setString(4, user.getSex());
        pstmt.setString(5, user.getTelnumber());
        pstmt.setString(6, user.getAccount());
//        pstmt.setDate(5, (Date) user.getBirthdate());
//        pstmt.setString(6, user.getTelnumber());
//        pstmt.setString(7, user.getAccount());
        //执行sql语句，返回影响行数
        int res=pstmt.executeUpdate();
        if(res>0){
            System.out.println("更新数据成功");
        }
        pstmt.close();
        connection.close();
        return true;
    }

    public boolean insertPatient(Patientbean user) throws SQLException {
        connection = getConnection();
//        String sql="update YY_PATIENT set password=?,name=?,idNumber=?,sex=?,birthDate=?,TELNUMBER=? where account=?";
        //预置对象YY_PATIENT
        String sql = "INSERT INTO YY_PATIENT (ACCOUNT,PASSWORD,NAME,IDNUMBER,SEX,BIRTHDATE,TELNUMBER) VALUES(?,?,?,?,?,?,?)";
        PreparedStatement pstmt=connection.prepareStatement(sql);
        Date dayDateSql = new Date(user.getBirthdate().getTime());
        //设置占位符值
        pstmt.setString(1, user.getAccount());
        pstmt.setString(2, user.getPassword());
        pstmt.setString(3, user.getName());
        pstmt.setString(4, user.getIdNumber());
        pstmt.setString(5, user.getSex());

        pstmt.setDate(6, dayDateSql );
        pstmt.setString(7, user.getTelnumber());
        int res=pstmt.executeUpdate();
        if(res>0){
            System.out.println("成功加入数据");
        }
        pstmt.close();
        connection.close();
        return true;
    }
}
